﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace cls.u_Models
{
    public partial class ShowPluPreElimination : System.Web.UI.Page
    {
        static String UserName = "";
        static String OrgCode = "";
        static String UserInOrgCode = "";
        static String UserInOrgId = "";
        static Boolean UserInZB = false;

        protected void Page_Load(object sender, EventArgs e)
        {
            UserName = User.Identity.Name;

            if (UserName.Equals(""))
            {
                Response.Redirect("..\\Default.aspx");
            }
            else
            {
                UserInOrgCode = GetOrgCodeByUserName(UserName);
                UserInOrgId = GetOrgIdByUserName(UserName);
            }

            if (UserInOrgCode.Trim().Equals("ZB"))
            {
                OrgCode = "all";
                UserInZB = true;

                //ClsRepons的DropDownList组件
                //DropDownList_ShowClsRepons();
            }
            else
            {
                OrgCode = UserInOrgCode;
                UserInZB = false;
            }

            if (!Page.IsPostBack)
            {
                //IssusepluDetailDataBind(UserInOrgCode);
                DropDownList_ShowOrgNames();
                //Label_HiddenClsRespons.Visible = UserInZB;
                //CheckBox_HiddenClsRespons.Visible = UserInZB;
            }

            //Button_Export.Enabled = false;
            //CheckBox_HiddenClsRespons.Style.Clear();
        }

        protected String GetOrgCodeByUserName(String UserName)
        {
            String OrgCode = "";

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT OrgCode FROM u_cls_Org WHERE Id = (SELECT OrgId FROM u_cls_UserInOrg WHERE UserName = @UserName)", SqlConnection);

            SelectCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 4);
            SelectCommand.Parameters["@UserName"].Value = UserName;

            SqlDataReader OrgCodeReader = SelectCommand.ExecuteReader();

            while (OrgCodeReader.Read())
            {
                //还是分开取得比较好，后面还有单独用到OrgCode的地方......
                //OrgCode = ResReader["OrgCode"].ToString() + "-" + ResReader["OrgName"].ToString();  
                OrgCode = OrgCodeReader["OrgCode"].ToString().Trim();
            }

            OrgCodeReader.Close();
            SqlConnection.Close();

            return OrgCode;
        }

        protected String GetOrgIdByUserName(String UserName)
        {
            String OrgId = "";

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT [OrgId] FROM [dbo].[u_cls_UserInOrg] WHERE [UserName] = @UserName", SqlConnection);

            SelectCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 4);
            SelectCommand.Parameters["@UserName"].Value = UserName;

            SqlDataReader OrgIdReader = SelectCommand.ExecuteReader();

            while (OrgIdReader.Read())
            {
                OrgId = OrgIdReader["OrgId"].ToString().ToUpper();
            }

            OrgIdReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();

            return OrgId;
        }

        protected String GetLastInsDatetime(String OrgCode)
        {
            String LastInsDatetime = "";
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT TOP(1) LastInsDatetime FROM [dbo].[u_cls_LastInsDatetime] WHERE ApplicationName = 'Issuseplu' AND OrgCode = @OrgCode ORDER BY LastInsDatetime DESC", SqlConnection);

            SelectCommand.Parameters.Add("@OrgCode", SqlDbType.NVarChar, 4);
            SelectCommand.Parameters["@OrgCode"].Value = OrgCode;

            SqlDataReader LastInsDatetimeReader = SelectCommand.ExecuteReader();

            //酱紫写法系错误嘀，乖乖的用While循环吧......
            //if(LastInsDatetimeReader.HasRows)
            //{
            //    LastInsDatetime = LastInsDatetimeReader["LastInsDatetime"].ToString();
            //}
            while (LastInsDatetimeReader.Read())
            {
                LastInsDatetime = LastInsDatetimeReader["LastInsDatetime"].ToString();
            }

            LastInsDatetimeReader.Close();
            SqlConnection.Close();

            return LastInsDatetime;
        }

        protected void DropDownList_ShowOrgNames()
        {
            DropDownList_SelectOrgCode.Items.Clear();

            String SelectCommandString = "";

            if (UserInOrgCode.Trim().Equals("ZB"))
            {
                DropDownList_SelectOrgCode.Items.Add(new ListItem("全部", "all"));
                SelectCommandString = "SELECT OrgCode,OrgName FROM [dbo].[u_cls_Org] ORDER BY OrgCode";
            }
            else
            {
                SelectCommandString = "SELECT OrgCode,OrgName FROM [dbo].[u_cls_Org] WHERE Id = '" + UserInOrgId + "' ORDER BY OrgCode";
            }

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand(SelectCommandString, SqlConnection);
            SqlDataReader OrgCodeReader = SelectCommand.ExecuteReader();

            while (OrgCodeReader.Read())
            {
                DropDownList_SelectOrgCode.Items.Add(new ListItem(OrgCodeReader["OrgCode"].ToString() + "-" + OrgCodeReader["OrgName"].ToString(), OrgCodeReader["OrgCode"].ToString()));
            }

            OrgCodeReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();
        }

        protected DataSet PluPreEliminationDetailDateSet(String OrgCode, String LastInsDatetime)
        {
            DataSet PluPreEliminationlDateSet = new DataSet();

            OracleConnection OraConnection = new OracleConnection("Data Source=hsczbcmp;User ID=hscmp;Password=Cmp_Data;Integrated Security=false;Persist Security Info=false");
            OraConnection.Open();

            //ImportDatetime的毫秒为0，LastInsDatetime的毫秒不是0
            //导致查询没有结果
            //String SelectCommandString = "SELECT [id],[OrgCode],[OrgName],[PluCode],[PluName],[Spec],[RetCount],[InvCount],[RetReason],[ClsRepons],[ProDate],[ExpDate],[ImportDatetime],[ImportUserName] FROM [dbo].[u_cls_Issuseplu_Table] WHERE [ImportDatetime] = '" + LastInsDatetime + "'";

            //使用SQL Server的MAX()函数重新构建语句
            //下面是正确的语句
            String SelectCommandString = "SELECT PluCode, PluName, EtpCode, HtCode, UpdateUserName, UpdateTime FROM tPluPreElimination";

            //是否选择了OrgCode
            //总部查看商品明细，不需要区分组织
            //if (OrgCode.Equals("all"))
            //{ }
            //else
            //{
            //    //SelectCommandString += " AND OrgCode='" + OrgCode + "'";
            //    SelectCommandString += " WHERE [u_cls_Issuseplu_Table].[OrgCode] ='" + OrgCode + "'";
            //}

            //是否隐藏已经回复的行
            //此处不适用
            //if (CheckBox_HiddenClsRespons.Checked)
            //{
            //    SelectCommandString += " AND [u_cls_Issuseplu_Table].[ClsRepons] = ''";
            //}

            SelectCommandString += " ORDER BY PluCode";
            
            OracleDataAdapter OraDa = new OracleDataAdapter(SelectCommandString, OraConnection);
            
            OraDa.Fill(PluPreEliminationlDateSet);

            OraDa.Dispose();
            OraConnection.Close();

            return PluPreEliminationlDateSet;
        }

        protected void Button_ShowPluPreEliminationDetail_Click(object sender, EventArgs e)
        {
            GridView_PluPreElimination.EmptyDataText = "您的查询条件没有结果。";

            UserInOrgCode = DropDownList_SelectOrgCode.SelectedValue;

            PluPreEliminationDetailDataBind(UserInOrgCode);
        }

        protected void PluPreEliminationDetailDataBind(String OrgCode)
        {
            GridView_PluPreElimination.AutoGenerateColumns = false;
            GridView_PluPreElimination.Columns.Clear();

            GridView_PluPreElimination.DataSource = PluPreEliminationDetailDateSet(OrgCode, GetLastInsDatetime(UserInOrgCode));
            
            //Oracle里面没有Id列
            //GridView_PluPreElimination.DataKeyNames = new string[] { "id" };

            BoundField bField;

            //bField = new BoundField();
            //bField.DataField = "id";
            //bField.HeaderText = "编号";
            //bField.ReadOnly = true;
            //bField.Visible = false;
            //GridView_PluPreElimination.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "OrgCode";
            //bField.HeaderText = "组织编码";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_PluPreElimination.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "OrgName";
            //bField.HeaderText = "组织名称";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_PluPreElimination.Columns.Add(bField);

            //[0]
            bField = new BoundField();
            bField.DataField = "PluCode";
            bField.HeaderText = "商品编码";
            bField.ReadOnly = true;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            GridView_PluPreElimination.Columns.Add(bField);

            //[1]
            bField = new BoundField();
            bField.DataField = "PluName";
            bField.HeaderText = "商品名称";
            bField.ReadOnly = true;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Left;
            GridView_PluPreElimination.Columns.Add(bField);

            //[2]
            bField = new BoundField();
            bField.DataField = "EtpCode";
            bField.HeaderText = "供应商编码";
            bField.ReadOnly = true;
            //bField.DataFormatString = "{0:D}";
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            GridView_PluPreElimination.Columns.Add(bField);

            //[3]
            bField = new BoundField();
            bField.DataField = "HtCode";
            bField.HeaderText = "合同编码";
            bField.ReadOnly = true;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            GridView_PluPreElimination.Columns.Add(bField);

            //[4]
            bField = new BoundField();
            bField.DataField = "UpdateUserName";
            bField.HeaderText = "上传用户";
            bField.ReadOnly = true;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            GridView_PluPreElimination.Columns.Add(bField);

            //[5]
            bField = new BoundField();
            bField.DataField = "UpdateTime";
            bField.HeaderText = "上传时间";
            bField.ReadOnly = UserInZB;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Left;
            GridView_PluPreElimination.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "ProDate";
            //bField.HeaderText = "生产日期";
            //bField.ReadOnly = UserInZB;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_PluPreElimination.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "ExpDate";
            //bField.HeaderText = "到期日期";
            //bField.ReadOnly = UserInZB;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_PluPreElimination.Columns.Add(bField);

            ////[11]
            //bField = new BoundField();
            //bField.DataField = "ClsRepons";
            //bField.HeaderText = "品类回复";
            //bField.ReadOnly = !UserInZB;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_PluPreElimination.Columns.Add(bField);

            //RetReason的List形式
            //参考资料
            //http://blog.sina.com.cn/s/blog_492faa8f0100di5x.html
            //http://msdn.microsoft.com/en-us/library/aa479353.aspx
            //http://msdn.microsoft.com/zh-cn/library/ms178294(v=vs.100).aspx
            //DropDownList或者ListBox形式
            //TemplateField tField = new TemplateField();
            //tField.ItemTemplate.InstantiateIn("?");
            //tField.EditItemTemplate.InstantiateIn(DropDownListClsRepons);
            //tField.HeaderText = "品类回复";
            //GridView_PluPreElimination.Columns.Add(tField);            

            //if (UserInZB)
            //{
            //    //bField = new BoundField();
            //    //bField.DataField = "LastModifyDatetime";
            //    //bField.HeaderText = "最后修改时间";
            //    //bField.ReadOnly = true;
            //    //GridView_PluPreElimination.Columns.Add(bField);

            //    bField = new BoundField();
            //    bField.DataField = "ImportDatetime";
            //    bField.HeaderText = "上传时间";
            //    bField.ReadOnly = true;
            //    GridView_PluPreElimination.Columns.Add(bField);

            //    bField = new BoundField();
            //    bField.DataField = "ImportUserName";
            //    bField.HeaderText = "上传用户";
            //    bField.HeaderStyle.Wrap = false;
            //    bField.ItemStyle.Wrap = false;
            //    bField.ReadOnly = true;
            //    GridView_PluPreElimination.Columns.Add(bField);
            //}

            CommandField cfEdit = new CommandField();
            cfEdit.ButtonType = ButtonType.Link;
            cfEdit.EditText = "编辑";
            cfEdit.ShowEditButton = true;
            cfEdit.UpdateText = "更新";
            cfEdit.CancelText = "取消";
            //cfEdit.DeleteText = "删除";
            cfEdit.ItemStyle.Wrap = false;

            //不加入编辑列
            //GridView_PluPreElimination.Columns.Add(cfEdit);

            //无效
            //GridView_PluPreElimination.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            GridView_PluPreElimination.HeaderStyle.Font.Size = 8;

            GridView_PluPreElimination.DataBind();

            //显示页脚合计
            //GridView_PluPreElimination_CosFooter();
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            //文件名只能是.xls，.xlsx会导致文件无法打开（Office 2013 X64）
            ExportToExcel(null, GridView_PluPreElimination, UserInOrgCode + "_" + UserName + "_" + DateTime.Now + "_导出.xls");
        }

        private void ExportToExcel(string FileType, GridView SourceGV, string FileName)
        {
            //参考资料
            //http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html
            //http://www.cnblogs.com/sparon/archive/2007/04/06/702946.html
            //
            //Office 2007 File Format MIME Types for HTTP Content Streaming
            //http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/05/08/office-2007-open-xml-mime-types.aspx

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader(
                "content-disposition", string.Format("attachment; filename={0}", FileName));

            //HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    //  Create a table to contain the grid
                    Table table = new Table();

                    //  include the gridline settings
                    table.GridLines = SourceGV.GridLines;

                    //  add the header row to the table
                    if (SourceGV.HeaderRow != null)
                    {
                        ShowPluPreElimination.PrepareControlForExport(SourceGV.HeaderRow);
                        table.Rows.Add(SourceGV.HeaderRow);
                    }

                    //  add each of the data rows to the table
                    foreach (GridViewRow row in SourceGV.Rows)
                    {
                        ShowPluPreElimination.PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }

                    //  add the footer row to the table
                    if (SourceGV.FooterRow != null)
                    {
                        ShowPluPreElimination.PrepareControlForExport(SourceGV.FooterRow);
                        table.Rows.Add(SourceGV.FooterRow);
                    }

                    //  render the table into the htmlwriter
                    table.RenderControl(htw);

                    //  render the htmlwriter into the response
                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();
                }
            }
        }

        private static void PrepareControlForExport(Control control)
        {
            for (int i = 0; i < control.Controls.Count; i++)
            {
                Control current = control.Controls[i];
                if (current is LinkButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                }
                else if (current is ImageButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                }
                else if (current is HyperLink)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                }
                else if (current is DropDownList)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                }
                else if (current is CheckBox)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                }

                if (current.HasControls())
                {
                    ShowPluPreElimination.PrepareControlForExport(current);
                }
            }
        }

        protected void Button_Return_Click(object sender, EventArgs e)
        {
            Response.Redirect("tPluPreElimination.aspx");
        }
    }
}